package util.tool.sql;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import util.model.common.DBConnModel;
import util.model.common.FieldInfo;
import util.model.common.TableInfo;
import util.util.DevComUtil;
import util.util.DevFileUtil;
import util.util.DevSqlUtil;
import util.util.excel.ReadExcel;
import util.util.excel.WriteExcel;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 读取数据库，获取表的字段，注释，类型，精度等
 */
public class DB2ExcelAllSheet {

    // 导出路径
    private static String rootPath = "D:\\91field\\";
    private static String localPath = rootPath + "allTable.xls";

    private static String aa = "";
    // 本地数据库
    private static String localUrl = "jdbc:oracle:thin:@//192.168.20.20:1521/orcl";
    private static String localDriver = "oracle.jdbc.driver.OracleDriver";
    private static String localUser = "stbddb";
    private static String localPassword = "golden";
    // 字段
    private static String[] fieldTitles = {"表名", "表名注释", "名称", "注释", "长度",
            "精度", "类型", "DB必填", "主键", "默认值"};
    private static String[] fieldNames = {"table_name", "table_comments", "name", "commentate", "length",
            "scale", "type", "is_d_b_must", "is_pk", "defaultValue"};

    public static void main(String[] args) {
//        DBConnModel loaclConn = new DBConnModel(localUrl, localDriver, localUser, localPassword);
//        String fieldSql = SqlUtil.getFieldSql();
//        System.out.println(fieldSql);
//        create(fieldSql, fieldTitles, fieldNames, localPath, loaclConn);
//        Sheet sheet = DevFileUtil.getSheet(aa, 1);
//        for (int i = 0; i < 159; i++) {
//            String cellValue = DevFileUtil.getCellValue(sheet.getRow(i), 0);
//            if (cellValue != null ) {
//                String fieldSql = SqlUtil.getFieldSql("BD_BA_DBXX");
//                // 本地--字段--Excel
//                create(fieldSql, fieldTitles, fieldNames, localPath, loaclConn);
//            }
//        }

//        aa();
        cc();
    }

    public static void aa() {
        String root = "D:\\90myWord\\软件和项目\\陕投资料\\陕投资料\\file\\";
        String fileName = "123.xlsx";
        String outTable = "123.xlsx";

        List<TableInfo> fieldModeDTOS = ReadExcel.read2Model(TableInfo.class, root + fileName, 2, 1);

        List<TableInfo> collect = fieldModeDTOS.stream().distinct().collect(Collectors.toList());

        WriteExcel.write(collect, root + outTable);


    }


    public static void bb() {
        String root = "D:\\90myWord\\软件和项目\\陕投资料\\陕投资料\\file\\";
        String fileName = "123.xlsx";
        String outTable = "123.xlsx";

        List<FieldInfo> fieldModeDTOS = ReadExcel.read2Model(FieldInfo.class, root + fileName, 2, 1);

        Map<String, List<FieldInfo>> collect = fieldModeDTOS.stream()
                .filter(item -> !DevComUtil.isEmpty(item.getTableName()))
                .filter(item -> !item.getTableName().contains("?"))
                .collect(Collectors.groupingBy(item -> item.getTableName(), Collectors.toList()));

        Workbook sheets = WriteExcel.model2Work2(collect, root + outTable);

        DevFileUtil.createExcle(sheets, root + "123.xlsx");


    }

    public static void cc() {
        String root = "D:\\90myWord\\软件和项目\\陕投资料\\陕投资料\\file\\";
        String outTable = "123.xlsx";

        List<FieldInfo> fieldModeDTOS = ReadExcel.read2Model(FieldInfo.class, root + outTable, 0, 1);

        Map<String, List<FieldInfo>> collect = fieldModeDTOS.stream()
                .collect(Collectors.groupingBy(item -> item.getFieldName(), Collectors.toList()));


        Set<Map.Entry<String, List<FieldInfo>>> entries = collect.entrySet();

        List<FieldInfo> list = new ArrayList<>();

        for (Map.Entry<String, List<FieldInfo>> entry : entries) {
            List<FieldInfo> value = entry.getValue();

            list.add(new FieldInfo());
            list.addAll(value);


        }

        WriteExcel.write(list, root + "789.xlsx");
    }


    /**
     * 创建
     * @param titles
     * @param names
     * @param path
     * @param dbConnModel
     */
    public static void create(String sql, String[] titles, String[] names, String path, DBConnModel dbConnModel) {
        Map<String, List<Map<String, String>>> dbFieldMap = getDBField(sql, titles, names, dbConnModel);
        toExecl(dbFieldMap, titles, names, path);
    }

    /**
     * 获取信息
     *
     * @return
     */
    private static Map<String, List<Map<String, String>>> getDBField(String sql, String[] titles, String[] names, DBConnModel dbConnModel) {
        Map<String, List<Map<String, String>>> excelMap = new TreeMap<>();
        List<Map<String, String>> list = null;
        Map<String, String> tableMap;
        //连接数据库
        Connection conn = DevSqlUtil.getConn(dbConnModel);
        ResultSet rs;
        try {
            list = new ArrayList<>();
            rs = DevSqlUtil.executeQuery(conn, sql);
            while (rs.next()) {
                tableMap = new HashMap<>();
                for (int j = 0; j < titles.length; j++) {
                    String str =  rs.getString(names[j]);
                    tableMap.put(names[j], str);
                }
                list.add(tableMap);
            }
            excelMap.put("field", list);
        } catch (Exception e) {
            //数据库驱动类异常处理
            System.out.println("Sorry,can`t find the Driver!");
            e.printStackTrace();
        }
        return excelMap;
    }


    /**
     * 生成Excel
     *
     * @param dbFieldMap
     */
    private static void toExecl(Map<String, List<Map<String, String>>> dbFieldMap, String[] titles, String[] names, String path) {
        HSSFWorkbook wb = new HSSFWorkbook();

        Iterator<Map.Entry<String, List<Map<String, String>>>> iterator = dbFieldMap.entrySet().iterator();
        while (iterator.hasNext()) {
            List<Map<String, String>> list = iterator.next().getValue();
            // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet

            Map<String, List<Map<String, String>>> collect = list.stream().collect(Collectors.groupingBy(item -> item.get("table_name")));

            Iterator<Map.Entry<String, List<Map<String, String>>>> it = collect.entrySet().iterator();
            while (it.hasNext()) {
                Map.Entry<String, List<Map<String, String>>> ne = it.next();
                List<Map<String, String>> va = ne.getValue();
                String sheetName = ne.getKey();
                HSSFSheet sheet = wb.createSheet(sheetName);
                //表头
                HSSFRow row = sheet.createRow(0);
                //声明列对象
                HSSFCell cell = null;
                //创建标题
                for (int i = 0; i < titles.length; i++) {
                    cell = row.createCell(i);
                    cell.setCellValue(titles[i]);
                }
                int rowCount = 0;
                for (Map<String, String> map : va) {
                    row = sheet.createRow(++rowCount);
                    for (int i = 0; i < names.length; i++) {
                        cell = row.createCell(i);
                        String value = map.get(names[i]);
                        cell.setCellValue(value);
                    }
                }
                System.out.println(sheetName);
            }
        }
        //生成
        DevFileUtil.createExcle(wb, path);
    }


}
